package com.hongdee.crm.card.service.impl;

import com.hongdee.common.dao.IHibernateDao;
import com.hongdee.common.dao.IImpHibernateDao;
import com.hongdee.common.dao.IImpJdbcDao;
import com.hongdee.common.dao.IJdbcDao;
import com.hongdee.common.util.TokenUtil;
import com.hongdee.crm.card.service.ITCrmCardService;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.stereotype.Service;
import org.springframework.util.StringUtils;

import java.util.ArrayList;
import java.util.HashMap;
import java.util.List;
import java.util.Map;

/**
 * Created by Liu XiaoLong
 * Date: 2018/2/27.
 * Remark:
 */
@Service
public class TCrmCardServiceImpl implements ITCrmCardService {

    @Autowired
    private IImpJdbcDao impJdbcDao;
    @Autowired
    private IImpHibernateDao impHibernateDao;

    @Autowired
    private IJdbcDao iJdbcDao;

    @Autowired
    private IHibernateDao hibernateDao;

    private String tabName = "T_CRM_CARD";
    private String OVERDUE = "2"; // 已过期
    private String NOOVERDUE = "1"; // 未过期

    @Override
    public Map impPage(String start, String limit, String oid) throws Exception {
        StringBuffer sqlBuffer = new StringBuffer();
        sqlBuffer.append("select * from " + tabName + " where 1=1 ");
        List<String> paramList = new ArrayList<String>();
        if (!StringUtils.isEmpty(oid)) {
            sqlBuffer.append(" and company_id = ? ");
            paramList.add(oid);
        }
        return this.impJdbcDao.queryForPageMap(start, limit, sqlBuffer.toString(), paramList.toArray());
    }

    /**
     * @Name: 全部确认
     * @Author: Liu XiaoLong
     * @Date: 2018/2/28 14:57
     * @Remark:
     */
    @Override
    public void impConfirmAll(String uid) throws Exception {
        //获取公司 ID
        String companyId = TokenUtil.getCompanyId(uid);
        //目标数据库
        String targetSchema = hibernateDao.Schema();
        //来源数据库
        String sourceSchema = impHibernateDao.Schema();

        if (StringUtils.isEmpty(targetSchema) || StringUtils.isEmpty(sourceSchema)) {
            throw new Exception("未获取到数据库实例请联系管理员");
        }

        //更新SQL
        StringBuffer sb = new StringBuffer();
        sb.append(" update " + targetSchema + ".t_crm_card c1,   ");
        sb.append("        " + sourceSchema + ".t_crm_card c2  ");
        sb.append("    SET c1.card_type= c2.card_type,  ");
        sb.append("        c1.end_date= c2.end_date,  ");
        sb.append("        c1.owner_name= c2.owner_name,  ");
        sb.append("        c1.sale_date= c2.sale_date,  ");
        sb.append("        c1.seller_name= c2.seller_name,  ");
        sb.append("        c1.start_date= c2.start_date,  ");
        sb.append("        c1.store_name= c2.store_name,  ");
        sb.append("        c1.tel_num= c2.tel_num,  ");
        sb.append("        c1.unit_type= c2.unit_type,  ");
        sb.append("        c1.value= c2.value  ");

        sb.append(" where  c1.card_num = c2.card_num and c1.item = c2.item ");
        sb.append("    and c1.company_id= c2.company_id   and c1.company_id = '" + companyId + "' ");
        //删除已存在记录
        StringBuffer delExistSQL = new StringBuffer();
        String targeTable = targetSchema + ".t_crm_card";
        String sourceTable = sourceSchema + ".t_crm_card";

        delExistSQL.append("delete from " + sourceTable + " USING " + targeTable + "," + sourceTable + " WHERE ");
        delExistSQL.append(" " + targeTable + ".company_id = " + sourceTable + ".company_id  and " + targeTable + ".card_num  = " + sourceTable + ".card_num  and " + targeTable + ".company_id ='" + companyId + "'");

        // 新增SQL
        StringBuffer selectField = new StringBuffer();
        selectField.append("   id,");
        selectField.append("   create_time,");
        selectField.append("   create_user,");
        selectField.append("   dept_id,");
        selectField.append("   update_time,");
        selectField.append("   update_user,");
        selectField.append("   card_num,");
        selectField.append("   card_type,");
        selectField.append("   end_date,");
        selectField.append("   owner_name,");
        selectField.append("   sale_date,");
        selectField.append("   seller_name,");
        selectField.append("   start_date,");
        selectField.append("   store_name,");
        selectField.append("   tel_num,");
        selectField.append("   unit_type,");
        selectField.append("   value,");
        selectField.append("   company_id,");
        selectField.append("   status,");
        selectField.append("   item");

        StringBuffer insertSQL = new StringBuffer();
        insertSQL.append(" insert into " + targetSchema + ".t_crm_card(").append(selectField.toString()).append(") ").append(" select ");
        insertSQL.append(selectField.toString()).append(" from " + sourceSchema + ".t_crm_card where  company_id = '" + companyId + "'");

        //更新记录数
        int upRowCount = iJdbcDao.update(sb.toString());
        System.out.println("-------------------更新优惠卡数-----updateCardNum----" + upRowCount);
        //删除更新记录数
        int delRowCount = iJdbcDao.update(delExistSQL.toString());
        System.out.println("-------------------删除更新优惠卡数-----deleteForUpdateCardNum----" + delRowCount);

        //新增记录数
        int addRowCount = iJdbcDao.update(insertSQL.toString());
        System.out.println("-------------------新增优惠卡数----insertCardNum-----" + addRowCount);

        //删除新增记录数
        int delAddRowCount = iJdbcDao.update(delExistSQL.toString());

        System.out.println("-------------------删除新增优惠卡数----deleteForInsertCardNum-----" + delAddRowCount);

        this.updateStatus(companyId); // 更新已经过期的卡
        this.updateUnitType(companyId); // 更新单位类型
    }

    @Override
    public void impDelAll(String uid) throws Exception {
        Map map = new HashMap();
        String companyId = TokenUtil.getCompanyId(uid);
        String hql = " delete from TCrmCard where companyId =:companyId";
        map.put("companyId", companyId);
        this.impHibernateDao.executeHqlWithParams(hql, map);
    }

    /**
     * @Name: 卡信息查询
     * @Author: Liu XiaoLong
     * @Date: 2018/3/1 10:24
     * @Remark:
     */
    @Override
    public Map queryCardPage(Map map, String uid,String start,String limit) throws Exception {
        String cardNum = (String) map.get("cardNum");
        List params = new ArrayList();
        String sql = " SELECT * FROM " + tabName + " WHERE 1=1 ";
        if(!cardNum.equals("")){
            sql += " AND card_num LIKE ? ";
            params.add("%"+cardNum+"%");
        }
        return this.iJdbcDao.queryForPageMap(start,limit,sql,params.toArray());
    }

    /**
     * @Name: 更新单位类型
     * @Author: Liu XiaoLong
     * @Date: 2018/3/27 13:50
     * @Remark:
     */

    private void updateUnitType(String companyId) throws Exception{
        // 更新单位类别
        String intSql = "UPDATE "+tabName+" SET unit_type = ( CASE card_type WHEN '美车卡' THEN '次' WHEN '贵宾卡' THEN '元' END ) WHERE company_id = "+companyId+" AND unit_type IS NULL ";
        int intCount = iJdbcDao.update(intSql);
        System.out.println("[Update the number of UnitType.]更新单位类型数量：" + intCount);
    }

    /**
     * @Name: 更新已过期的卡
     * @Author: Liu XiaoLong
     * @Date: 2018/2/28 15:57
     * @Remark:
     */
    private void updateStatus(String companyId) throws Exception{
        // 初始化所有状态为空的为未过期
        String intSql = "UPDATE "+tabName+" SET status = "+NOOVERDUE+" WHERE company_id = "+companyId+" AND status IS NULL ";
        int intCount = iJdbcDao.update(intSql);
        System.out.println("[Init the number of all card.]初始化优惠卡数量：" + intCount);

        String sql = "UPDATE "+tabName+" SET status = "+OVERDUE+" WHERE FROM_UNIXTIME(end_date/1000,'%Y-%m-%d') < DATE(NOW()) AND company_id = "+companyId+" AND end_date IS NOT NULL ";
        int count = iJdbcDao.update(sql);
        System.out.println("[Update the number of expired card.]更新过期优惠卡数量：" + count);
    }
/**
 * @Name: 全部删除
 * @Author: Liu XiaoLong
 * @Date: 2018/2/28 16:55
 * @Remark:
 */
   /* @Override
    public void impDelAll(String uid) throws Exception {
        Map map = new HashMap();
        String companyId = TokenUtil.getCompanyId(uid);
        String hql = " delete from TCrmCard where companyId =:companyId";
        map.put("companyId", companyId);
        this.impHibernateDao.executeHqlWithParams(hql, map);
    }*/

}